package com.vhome.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.vhome.beans.Child;
import com.vhome.beans.User;

public class dbManager {

	public static Connection getconnection() throws SQLException {
		Connection conn = null;
		try {
			// http://www.devdaily.com/java/edu/JDBC-SQLProcessor/Simple_JDBC_Example.shtml
			// The newInstance() call is a work around for some
			// broken Java implementations
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) { // handle the error
			ex.printStackTrace();
		}

		conn = DriverManager.getConnection("jdbc:mysql://localhost/babygo?"
				+ "user=tony&password=haha13");// VancouverTongue,yiyeh
		return conn;
	}

	public static User login(String userId) throws SQLException {
		// ***Initialised
		User tempUser = new User();
		Connection conn = getconnection();
		Statement statement = conn.createStatement();

		String sql = "select userid,username,password from account where username= "
				+ '"' + userId + '"';

		ResultSet rs = statement.executeQuery(sql);

		// write in data
		while (rs.next()) {
			tempUser.setUserid( rs.getString("userid") );
			tempUser.setUsername( rs.getString("username"));
			tempUser.setPassword( rs.getString("password"));
		}
		
		conn.close();
		return tempUser;
	}
	
	public static ArrayList<Child> getAllChild(String parentID) throws SQLException {
		// ***Initialised
		ArrayList<Child> childlist = new ArrayList<Child>();
		Child tempChild = new Child();
		Connection conn = getconnection();
		Statement statement = conn.createStatement();

		String sql = "select id,parentid,name, age, gender, info, pic_path where parentid= "
				+ '"' + parentID + '"';

		ResultSet rs = statement.executeQuery(sql);

		// write in data
		while (rs.next()) {
			tempChild.setCid( rs.getString("id") );
			tempChild.setChildparentid(rs.getString("parentid"));
			tempChild.setChildname(rs.getString("name"));
			tempChild.setChildage(rs.getInt("age"));
			tempChild.setChildgender(rs.getInt("gender"));
			tempChild.setChildinfo(rs.getString("info"));
			tempChild.setChildPicPath(rs.getString("pic_path"));
			childlist.add(tempChild);
		}
		
		conn.close();
		return childlist;
	}
	
	public static void main(String[] args) {
		
	}

}
